set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = 'nonstrict';
set hive.exec.max.dynamic.partitions = 400;
set hive.exec.max.dynamic.partitions.pernode = 200;
with taketimely_rate as (--未及时揽收
    select waybill_id as waybill_no         --取订单表
--          , customer_code --取订单表
--          , customer_name --取订单表
         , dt
         , null       as deliver_aging_sign_difftime
         , null       as proble_type_subject_code
         , null       as proble_type_subject_name
         , 1          as no_taketimely_flag --1未及时揽收
         , 0          as no_collect_flag    --1不准点交件票
         , 0          as false_taking_flag  --1虚假揽收
         , 0          as no_outtimely_flag  --1出港不及时
         , 0          as no_signtimely_flag --1未及时签收
         , 0          as complaint_flag     --1客诉量
         , 0          as sign_difftime_flag --1网点出仓-时效签收
         , 0          as problem_flag       --1问题件
    from jms_dm.dm_agent_taketimely_rate_detail --订单及时揽收率
    where dt <= '{{ execution_date | cst_ds }}' --分区(订单录入时间)(回刷7天)(运单)
      and dt >= date_add('{{ execution_date | cst_ds }}', -6)
),

     collect_intime as (--(不准点交件票数)
         select waybill                    --取未签收宽表
--               , customer_code --取未签收宽表
--               , customer_name --取未签收宽表
              , dt
              , null as deliver_aging_sign_difftime
              , null as proble_type_subject_code
              , null as proble_type_subject_name
              , 0    as no_taketimely_flag --1未及时揽收
              , 1    as no_collect_flag    --1不准点交件票
              , 0    as false_taking_flag  --1虚假揽收
              , 0    as no_outtimely_flag  --1出港不及时
              , 0    as no_signtimely_flag --1未及时签收
              , 0    as complaint_flag     --1客诉量
              , 0    as sign_difftime_flag --1网点出仓-时效签收
              , 0    as problem_flag       --1问题件
         from jms_dm.dm_waybill_collect_intime --交件准点率
         where dt <= '{{ execution_date | cst_ds }}' --分区(揽收时间)(回刷2天)(上层7天)(运单)
           and dt >= date_add('{{ execution_date | cst_ds }}', -6)
           and is_over is not null
           and is_over <> 1
           and type = 0
     ),
     false_taking as (--虚假揽收
         select waybill_no                 --取dm_waybill_collect_intime(未签收宽表）
--               , customer_code --取dm_waybill_collect_intime(未签收宽表）
--               , customer_name --取dm_waybill_collect_intime(未签收宽表）
              , dt
              , null as deliver_aging_sign_difftime
              , null as proble_type_subject_code
              , null as proble_type_subject_name
              , 0    as no_taketimely_flag --1未及时揽收
              , 0    as no_collect_flag    --1不准点交件票
              , 1    as false_taking_flag  --1虚假揽收
              , 0    as no_outtimely_flag  --1出港不及时
              , 0    as no_signtimely_flag --1未及时签收
              , 0    as complaint_flag     --1客诉量
              , 0    as sign_difftime_flag --1网点出仓-时效签收\
              , 0    as problem_flag       --1问题件
         from jms_dm.dm_waybill_false_taking_dt -- 虚假揽收
         where dt <= '{{ execution_date | cst_ds }}' --分区(揽收时间)(回刷8天)(运单)
           and dt >= date_add('{{ execution_date | cst_ds }}', -6)
     ),
     departure_transfer as ( --不及时票数
         select billcode                   --中心发件
              , dt
              , null as deliver_aging_sign_difftime
              , null as proble_type_subject_code
              , null as proble_type_subject_name
              , 0    as no_taketimely_flag --1未及时揽收
              , 0    as no_collect_flag    --1不准点交件票
              , 0    as false_taking_flag  --1虚假揽收
              , 1    as no_outtimely_flag  --1出港不及时
              , 0    as no_signtimely_flag --1未及时签收
              , 0    as complaint_flag     --1客诉量
              , 0    as sign_difftime_flag --1网点出仓-时效签收
              , 0    as problem_flag       --1问题件
         from jms_dm.dm_cn_departure_transfer_timely_route_dt --出港转运及时率
         where dt <= '{{ execution_date | cst_ds }}'--分区(t-2,t-3)(回刷2天)(任务表的实际到达时间t-2 14点-t-1 14点)(运单+中心)
           and dt >= date_add('{{ execution_date | cst_ds }}', -6)
           and untimely_cause not in (1, 2, 11, 8)
           and center_type = 1
         group by billcode --中心发件
                , dt
     ),
     terminal_sign as ( --未及时签收
         select billcode
              , dt
              , null as deliver_aging_sign_difftime
              , null as proble_type_subject_code
              , null as proble_type_subject_name
              , 0    as no_taketimely_flag --1未及时揽收
              , 0    as no_collect_flag    --1不准点交件票
              , 0    as false_taking_flag  --1虚假揽收
              , 0    as no_outtimely_flag  --1出港不及时
              , 1    as no_signtimely_flag --1未及时签收
              , 0    as complaint_flag     --1客诉量
              , 0    as sign_difftime_flag --1网点出仓-时效签收
              , 0    as problem_flag       --1问题件
         from jms_dm.dm_terminal_sign_punctuality_rate_detail_dt --时效签收准点率
         where dt <= '{{ execution_date | cst_ds }}'               --分区(规划签收时间)(回刷t-1)(运单)
           and dt >= date_add('{{ execution_date | cst_ds }}', -6) --分区(规划签收时间)(回刷t-1)(运单)
           and if(date_format(aging_sign_time, 'yyyy-MM-dd HH') <= concat(to_date(final_plan_sign_time), ' 22'), 1,
                  0) = 0
     ),
     complaint_detail as (--客诉量
         select waybill_no
              , dt
              , null as deliver_aging_sign_difftime
              , null as proble_type_subject_code
              , null as proble_type_subject_name
              , 0    as no_taketimely_flag --1未及时揽收
              , 0    as no_collect_flag    --1不准点交件票
              , 0    as false_taking_flag  --1虚假揽收
              , 0    as no_outtimely_flag  --1出港不及时
              , 0    as no_signtimely_flag --1未及时签收
              , 1    as complaint_flag     --1客诉量
              , 0    as sign_difftime_flag --1网点出仓-时效签收
              , 0    as problem_flag       --1问题件
         from jms_dm.dm_waybill_customer_complaint_detail_new_dt --客诉率
         where dt <= '{{ execution_date | cst_ds }}' --分区(创建时间)(回刷当月)(运单)
           and dt >= date_add('{{ execution_date | cst_ds }}', -6)
           and is_delete = 2
     ),
     whole_effect as (--网点出仓-时效签收
         select waybill_no
              , sign_date                      as dt
              , if(deliver_aging_sign_difftime > 0
             , deliver_aging_sign_difftime, 0) as deliver_aging_sign_difftime --时效签收和末端网点派件时间差
              , null                           as proble_type_subject_code
              , null                           as proble_type_subject_name
              , 0                              as no_taketimely_flag          --1未及时揽收
              , 0                              as no_collect_flag             --1不准点交件票
              , 0                              as false_taking_flag           --1虚假揽收
              , 0                              as no_outtimely_flag           --1出港不及时
              , 0                              as no_signtimely_flag          --1未及时签收
              , 0                              as complaint_flag              --1客诉量
              , 1                              as sign_difftime_flag          --1网点出仓-时效签收
              , 0                              as problem_flag                --1问题件
         from jms_dws.dws_wide_unsign_whole_effect_dt --派件环节时效
         where dt between date_add('{{ execution_date | cst_ds }}', -36) and '{{ execution_date | cst_ds }}' --分区(实际揽收日期)(汇总是签收时间)(运单)
           and send_network_code is not null
           and sign_date <= '{{ execution_date | cst_ds }}'
           and sign_date >= date_add('{{ execution_date | cst_ds }}', -6)
           and deliver_aging_sign_difftime > 24
     ),
     problem_piece as (--问题件
         select waybill_no
              , dt
              , null                                  as deliver_aging_sign_difftime --派签时间差
              , collect_set(proble_type_subject_code) as proble_type_subject_code    --一级类型code 41遗失件 27破损件 35被污染件
              , collect_set(proble_type_subject_name) as proble_type_subject_name    --一级类型name
              , 0                                     as no_taketimely_flag          --1未及时揽收
              , 0                                     as no_collect_flag             --1不准点交件票
              , 0                                     as false_taking_flag           --1虚假揽收
              , 0                                     as no_outtimely_flag           --1出港不及时
              , 0                                     as no_signtimely_flag          --1未及时签收
              , 0                                     as complaint_flag              --1客诉量
              , 0                                     as sign_difftime_flag          --1网点出仓-时效签收
              , 1                                     as problem_flag                --1问题件
         from jms_dwd.dwd_sqs_registration_problem_piece_base_dt t
         where dt <= '{{ execution_date | cst_ds }}' --分区(创建时间)(回刷30天)
           and dt >= date_add('{{ execution_date | cst_ds }}', -6)
           and t.proble_type_subject_code is not null
         group by waybill_no
                , dt
     ),
     union_all_table as (
         select waybill_no
              , dt
              , max(deliver_aging_sign_difftime) as deliver_aging_sign_difftime --派签时间差
              , max(proble_type_subject_code)    as proble_type_subject_code    --一级类型code 41遗失 27破损 35被污染 999其他
              , max(proble_type_subject_name)    as proble_type_subject_name    --一级类型name
              , max(no_taketimely_flag)          as no_taketimely_flag          --1未及时揽收
              , max(no_collect_flag)             as no_collect_flag             --1不准点交件票
              , max(false_taking_flag)           as false_taking_flag           --1虚假揽收
              , max(no_outtimely_flag)           as no_outtimely_flag           --1出港不及时
              , max(no_signtimely_flag)          as no_signtimely_flag          --1未及时签收
              , max(complaint_flag)              as complaint_flag              --1客诉量
              , max(sign_difftime_flag)          as sign_difftime_flag          --1网点出仓-时效签收
              , max(problem_flag)                as problem_flag                --1问题件
         from (
                  select *
                  from taketimely_rate
                  union all
                  select *
                  from collect_intime
                  union all
                  select *
                  from false_taking
                  union all
                  select *
                  from departure_transfer
                  union all
                  select *
                  from terminal_sign
                  union all
                  select *
                  from complaint_detail
                  union all
                  select *
                  from whole_effect
                  union all
                  select *
                  from problem_piece
              ) a
         group by waybill_no
                , dt
     )
insert
overwrite
table
jms_dm.dm_cusc_customer_abnormal_waybill_detail_dt
partition
(
dt
)
select t1.waybill_no                         as waybill_no                  --运单
     , t1.deliver_aging_sign_difftime        as deliver_aging_sign_difftime --派签时间差
     , t1.proble_type_subject_code           as proble_type_subject_code    --一级类型code41遗失 27破损 35被污染 999其他
     , t1.proble_type_subject_name           as proble_type_subject_name    --一级类型name
     , t1.no_taketimely_flag                 as no_taketimely_flag          --1未及时揽收
     , t1.no_collect_flag                    as no_collect_flag             --1不准点交件票
     , t1.false_taking_flag                  as false_taking_flag           --1虚假揽收
     , t1.no_outtimely_flag                  as no_outtimely_flag           --1出港不及时
     , t1.no_signtimely_flag                 as no_signtimely_flag          --1未及时签收
     , t1.complaint_flag                     as complaint_flag              --1客诉量
     , t1.sign_difftime_flag                 as sign_difftime_flag          --1网点出仓-时效签收
     , t1.problem_flag                       as problem_flag                --1问题件
     , t2.order_id                           as order_id                    --订单号
     , t2.customer_id                        as customer_id                 --客户id
     , t2.customer_code                      as customer_code               --客户编码
     , t2.customer_name                      as customer_name               --客户名称
     , t2.customer_network_code              as customer_network_code       --客户所属网点code
     , t2.customer_network_name              as customer_network_name       --客户所属网点name
     , t2.order_source_code                  as order_source_code           --订单来源code
     , t2.order_source_name                  as order_source_name           --订单来源名称
     , t2.input_time                         as input_time                  --订单录入时间
     , t2.way_input_time                     as way_input_time              --运单录入时间
     , t2.deliver_early_scantime             as deliver_early_scantime      --宽表第一次派件时间
     , t2.sign_early_scantime                as sign_early_scantime         --宽表第一次签收时间
     , t2.taking_early_scansitecode          as taking_early_scansitecode   --宽表最早揽收网点
     , t4.name                               as taking_early_scansitename   --宽表最早揽收网点name
     , t2.sign_code                          as sign_code                   --宽表第一次签收网点
     , t5.name                               as sign_name                   --宽表第一次签收网点name
     , t3.fran_code                          as customer_fran_code          --客户所属加盟商code
     , t3.fran_name                          as customer_fran_name          --客户所属加盟商name
     , t3.agent_code                         as customer_agent_code         --客户所属代理区code
     , t3.agent_name                         as customer_agent_name         --客户所属代理区name
     , t4.fran_code                          as start_fran_code             --始发网点加盟商code
     , t4.fran_name                          as start_fran_name             --始发网点加盟商name
     , t4.agent_code                         as start_agent_code            --始发网点代理区code
     , t4.agent_name                         as start_agent_name            --始发网点代理区name
     , t4.provider_id                        as start_provider_id           --始发网点省id
     , t4.provider_desc                      as start_provider_desc         --始发网点省name
     , t4.city_id                            as start_city_id               --始发网点市id
     , t4.city_desc                          as start_city_desc             --始发网点市name
     , t4.area_id                            as start_area_id               --始发网点区id
     , t4.area_desc                          as start_area_desc             --始发网点区name
     , t5.fran_code                          as end_fran_code               --目的网点加盟商code
     , t5.fran_name                          as end_fran_name               --目的网点加盟商name
     , t5.agent_code                         as end_agent_code              --目的网点代理区code
     , t5.agent_name                         as end_agent_name              --目的网点代理区name
     , coalesce(t5.virt_code, t5.agent_code) as end_virt_code               --目的网点虚拟代理区code
     , coalesce(t5.virt_name, t5.agent_name) as end_virt_name               --目的网点虚拟代理区name
     , t5.provider_id                        as end_provider_id             --目的网点省id
     , t5.provider_desc                      as end_provider_desc           --目的网点省name
     , t5.city_id                            as end_city_id                 --目的网点市id
     , t5.city_desc                          as end_city_desc               --目的网点市name
     , t5.area_id                            as end_area_id                 --目的网点区id
     , t5.area_desc                          as end_area_desc               --目的网点区name
     , t1.dt                                 as date_time                   --日期
     --新增
     , t2.taking_early_scantime              as taking_early_scantime       --揽收时间
     , case
           when t2.taking_early_scantime > t2.input_time and t2.input_time is not null and
                t2.taking_early_scantime is not null
               then (unix_timestamp(t2.taking_early_scantime) - unix_timestamp(t2.input_time)) / 3600
    end                                      as diff_take_input_hour        --揽收时长(小时)
     , t1.dt
from union_all_table t1
         left join
     (
         select order_id
              , waybill_id                                               as waybill_no
              , customer_id
              , customer_code
              , customer_name
              , customer_network_code
              , customer_network_name
              , case
                    when order_source_code = 'D801' then 'D801'
                    when order_source_code = 'D67' then 'D67'
                    when order_source_code = 'D69' then 'D69'
                    when order_source_code = 'D190' then 'D190'
                    when order_source_code = 'D619' then 'D619'
                    when order_source_code = 'D553' then 'D553'
                    when order_source_code = 'D501' then 'D501'
                    else 'D999999' end                                   as order_source_code
              , case
                    when order_source_code = 'D801' then '七星潭'
                    when order_source_code = 'D67' then '紫金山'
                    when order_source_code = 'D69' then '桃花岛'
                    when order_source_code = 'D190' then '逍遥峰'
                    when order_source_code = 'D619' then '极地湾'
                    when order_source_code = 'D553' then '有赞'
                    when order_source_code = 'D501' then '苏宁易购'
                    else '其他' end                                        as order_source_name
              , input_time
              , way_input_time
              , deliver_early_scantime
              , sign_early_scantime
              , taking_early_scansitecode
              , taking_early_scantime --揽收时间
              , coalesce(sign_early_scansitecode, dispatch_network_code) as sign_code
         from jms_dwd.dwd_wide_customer_order_waybill_dt
         where dt <= '{{ execution_date | cst_ds }}'
           and dt >= date_add('{{ execution_date | cst_ds }}', - 44)
     ) t2
     on t1.waybill_no = t2.waybill_no
         left join jms_dim.dim_network_whole_massage t3
                   on t2.customer_network_code = t3.code
         left join jms_dim.dim_network_whole_massage t4
                   on t2.taking_early_scansitecode = t4.code
         left join jms_dim.dim_network_whole_massage t5
                   on t2.sign_code = t5.code
    distribute by dt,pmod(hash(rand()),10)
;